/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Juliane Chaerki
 */
public class ClienteContaDAO {
    private Connection con;
    private PreparedStatement stmtBuscarCpf;
    private PreparedStatement stmtBuscarRg;
    private PreparedStatement stmtBuscarNome;
    private PreparedStatement stmtBuscarSobrenome;
    private PreparedStatement stmtBuscarNconta;
    
    public ClienteContaDAO() throws Exception {
        con = ConFactory.getConnection();
        stmtBuscarCpf = con.prepareStatement("SELECT * FROM cliente WHERE cpf = ? AND nconta != 0");
        stmtBuscarRg = con.prepareStatement("SELECT * FROM cliente WHERE rg = ? AND nconta != 0");
        stmtBuscarNome = con.prepareStatement("SELECT * FROM cliente WHERE nome LIKE '% ? %' AND nconta != 0");
        stmtBuscarSobrenome = con.prepareStatement("SELECT * FROM cliente WHERE sobrenome LIKE '%?%' AND nconta != 0");
        stmtBuscarNconta = con.prepareStatement("SELECT tipo FROM conta WHERE nconta = ? AND nconta != 0");
    }
    
    public ClienteContaDAO(Connection con) throws Exception {
        this.con = con;
        stmtBuscarCpf = con.prepareStatement("SELECT * FROM cliente WHERE cpf = ? AND nconta != 0");
        stmtBuscarRg = con.prepareStatement("SELECT * FROM cliente WHERE rg = ? AND nconta != 0");
        stmtBuscarNome = con.prepareStatement("SELECT * FROM cliente WHERE nome LIKE '% ? %' AND nconta != 0");
        stmtBuscarSobrenome = con.prepareStatement("SELECT * FROM cliente WHERE sobrenome LIKE '%?%' AND nconta != 0");
        stmtBuscarNconta = con.prepareStatement("SELECT tipo FROM conta WHERE nconta = ? AND nconta != 0");
    }
    
    
    
     public List<ClienteConta> busca(String busca, int parametro) throws Exception{
    	List<ClienteConta> listaCliConta = new ArrayList();
        switch (parametro){
    		//cpf
    		case 0: {
    			stmtBuscarCpf.setString(1, busca);
    			ResultSet rs = stmtBuscarCpf.executeQuery();
    			if (rs.next()){
                            ClienteConta clienteBuscado = new ClienteConta(rs.getInt("nconta"),rs.getString("nome"), rs.getString("sobrenome"));
                            stmtBuscarNconta.setInt(1,rs.getInt("nconta"));
                            ResultSet res = stmtBuscarNconta.executeQuery();
                            res.next();
                            clienteBuscado.setTipo(Integer.toString(res.getInt("tipo")));                            
                            listaCliConta.add(clienteBuscado);
                        }
                        return listaCliConta;
    		}
    		//rg
    		case 1: {
    			stmtBuscarRg.setString(1, busca);
    			ResultSet rs = stmtBuscarRg.executeQuery();
    			if (rs.next()){
                            ClienteConta clienteBuscado = new ClienteConta(rs.getInt("nconta"),rs.getString("nome"), rs.getString("sobrenome"));
                            stmtBuscarNconta.setInt(1,rs.getInt("nconta"));
                            ResultSet res = stmtBuscarNconta.executeQuery();
                            res.next();
                            clienteBuscado.setTipo(Integer.toString(res.getInt("tipo")));                            
                            listaCliConta.add(clienteBuscado);
                        }
                        return listaCliConta;
    		}
    		//nome
    		case 2: {
                        String str = "SELECT * FROM cliente WHERE nome LIKE '%" + busca + "%' AND nconta != 0";
    			ResultSet rs = stmtBuscarNome.executeQuery(str);
    			while(rs.next()){
                            ClienteConta clienteBuscado = new ClienteConta(rs.getInt("nconta"),rs.getString("nome"), rs.getString("sobrenome"));
                            stmtBuscarNconta.setInt(1,rs.getInt("nconta"));
                            ResultSet res = stmtBuscarNconta.executeQuery();
                            res.next();
                            clienteBuscado.setTipo(Integer.toString(res.getInt("tipo")));                            
                            listaCliConta.add(clienteBuscado);
                        }
                        return listaCliConta;                
    		}
    		//sobrenome
                case 3: {
                        String str = "SELECT * FROM cliente WHERE sobrenome LIKE '%" + busca + "%'  AND nconta != 0";
    			ResultSet rs = stmtBuscarSobrenome.executeQuery(str);
    			while(rs.next()){
                            ClienteConta clienteBuscado = new ClienteConta(rs.getInt("nconta"),rs.getString("nome"), rs.getString("sobrenome"));
                            stmtBuscarNconta.setInt(1,rs.getInt("nconta"));
                            ResultSet res = stmtBuscarNconta.executeQuery();
                            res.next();
                            clienteBuscado.setTipo(Integer.toString(res.getInt("tipo")));                            
                            listaCliConta.add(clienteBuscado);
                        }
                        return listaCliConta;
    		}
                default: return null;
    	}
    }
     
         @Override
    public void finalize() throws Throwable{
        super.finalize();
        stmtBuscarCpf.close();
        stmtBuscarRg.close();
        stmtBuscarNome.close();
        stmtBuscarSobrenome.close();
        stmtBuscarNconta.close();
        con.close();
    }
}
